{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f06f00b",
   "metadata": {},
   "source": [
    "```\n",
    "Licensed to the Apache Software Foundation (ASF) under one\n",
    "or more contributor license agreements.  See the NOTICE file\n",
    "distributed with this work for additional information\n",
    "regarding copyright ownership.  The ASF licenses this file\n",
    "to you under the Apache License, Version 2.0 (the\n",
    "\"License\"); you may not use this file except in compliance\n",
    "with the License.  You may obtain a copy of the License at\n",
    "  http://www.apache.org/licenses/LICENSE-2.0\n",
    "Unless required by applicable law or agreed to in writing,\n",
    "software distributed under the License is distributed on an\n",
    "\"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY\n",
    "KIND, either express or implied.  See the License for the\n",
    "specific language governing permissions and limitations\n",
    "under the License.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66a56fcb-27a5-4b37-a913-05a8f887d22f",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Understand Overture Map data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92984a1c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sedona.spark import *\n",
    "import os\n",
    "import time\n",
    "import gresearch.spark.parquet\n",
    "import geopandas as gpd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cfd484a-e6ac-4cdb-a8ac-2ed77fbd23db",
   "metadata": {},
   "source": [
    "### Wherobots version\n",
    "\n",
    "Wherobots only releases a version for overturemaps-us-west-2/release/2023-07-26-alpha.0/.\n",
    "\n",
    "This data is in GeoParquet format and data is clustered by their spatial proximity to ensure efficient filter pushdown performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e4392353",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "DATA_LINK = \"s3a://wherobots-examples/data/overturemaps-us-west-2/release/2023-07-26-alpha.0/\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5fcf043-a97d-4321-ac16-fafcdbbcf3aa",
   "metadata": {},
   "source": [
    "### OMF versions\n",
    "\n",
    "The following files are official OMF releases. They are GeoParquet files generated by Apache Sedona.\n",
    "\n",
    "However, unlike the Wherobots version, these data might not be in the same file structures and hence the spatial query might be a bit slower.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "937cd401-f973-45dc-aabe-735d9a41b4c2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# DATA_LINK = \"s3a://overturemaps-us-west-2/release/2023-11-14-alpha.0/\"\n",
    "# DATA_LINK = \"s3a://overturemaps-us-west-2/release/2023-12-14-alpha.0/\"\n",
    "# DATA_LINK = \"s3a://overturemaps-us-west-2/release/2024-01-17-alpha.0/\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9ddd6e7-6e7e-4f2c-b2ae-83ddf56ed198",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Create Sedona Context"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "08c71acb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning: Ignoring non-Spark config property: fs.s3a.aws.credentials.provider\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ":: loading settings :: url = jar:file:/home/jovyan/spark-3.4.2-bin-hadoop3/jars/ivy-2.5.1.jar!/org/apache/ivy/core/settings/ivysettings.xml\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Ivy Default Cache set to: /home/jovyan/.ivy2/cache\n",
      "The jars for the packages stored in: /home/jovyan/.ivy2/jars\n",
      "org.apache.sedona#sedona-spark-3.4_2.12 added as a dependency\n",
      "org.datasyslab#geotools-wrapper added as a dependency\n",
      "uk.co.gresearch.spark#spark-extension_2.12 added as a dependency\n",
      ":: resolving dependencies :: org.apache.spark#spark-submit-parent-32c6a229-d04d-446c-b16d-58a8e96cbc3f;1.0\n",
      "\tconfs: [default]\n",
      "\tfound org.apache.sedona#sedona-spark-3.4_2.12;1.6.0 in central\n",
      "\tfound org.apache.sedona#sedona-common;1.6.0 in central\n",
      "\tfound org.apache.commons#commons-math3;3.6.1 in central\n",
      "\tfound org.locationtech.jts#jts-core;1.19.0 in central\n",
      "\tfound org.wololo#jts2geojson;0.16.1 in central\n",
      "\tfound org.locationtech.spatial4j#spatial4j;0.8 in central\n",
      "\tfound com.google.geometry#s2-geometry;2.0.0 in central\n",
      "\tfound com.google.guava#guava;25.1-jre in central\n",
      "\tfound com.google.code.findbugs#jsr305;3.0.2 in central\n",
      "\tfound org.checkerframework#checker-qual;2.0.0 in central\n",
      "\tfound com.google.errorprone#error_prone_annotations;2.1.3 in central\n",
      "\tfound com.google.j2objc#j2objc-annotations;1.1 in central\n",
      "\tfound org.codehaus.mojo#animal-sniffer-annotations;1.14 in central\n",
      "\tfound com.uber#h3;4.1.1 in central\n",
      "\tfound net.sf.geographiclib#GeographicLib-Java;1.52 in central\n",
      "\tfound com.github.ben-manes.caffeine#caffeine;2.9.2 in central\n",
      "\tfound org.checkerframework#checker-qual;3.10.0 in central\n",
      "\tfound com.google.errorprone#error_prone_annotations;2.5.1 in central\n",
      "\tfound org.apache.sedona#sedona-spark-common-3.4_2.12;1.6.0 in central\n",
      "\tfound commons-lang#commons-lang;2.6 in central\n",
      "\tfound org.scala-lang.modules#scala-collection-compat_2.12;2.5.0 in central\n",
      "\tfound org.beryx#awt-color-factory;1.0.0 in central\n",
      "\tfound org.datasyslab#geotools-wrapper;1.6.0-28.2 in central\n",
      "\tfound uk.co.gresearch.spark#spark-extension_2.12;2.11.0-3.4 in central\n",
      "\tfound com.github.scopt#scopt_2.12;4.1.0 in central\n",
      ":: resolution report :: resolve 2297ms :: artifacts dl 105ms\n",
      "\t:: modules in use:\n",
      "\tcom.github.ben-manes.caffeine#caffeine;2.9.2 from central in [default]\n",
      "\tcom.github.scopt#scopt_2.12;4.1.0 from central in [default]\n",
      "\tcom.google.code.findbugs#jsr305;3.0.2 from central in [default]\n",
      "\tcom.google.errorprone#error_prone_annotations;2.5.1 from central in [default]\n",
      "\tcom.google.geometry#s2-geometry;2.0.0 from central in [default]\n",
      "\tcom.google.guava#guava;25.1-jre from central in [default]\n",
      "\tcom.google.j2objc#j2objc-annotations;1.1 from central in [default]\n",
      "\tcom.uber#h3;4.1.1 from central in [default]\n",
      "\tcommons-lang#commons-lang;2.6 from central in [default]\n",
      "\tnet.sf.geographiclib#GeographicLib-Java;1.52 from central in [default]\n",
      "\torg.apache.commons#commons-math3;3.6.1 from central in [default]\n",
      "\torg.apache.sedona#sedona-common;1.6.0 from central in [default]\n",
      "\torg.apache.sedona#sedona-spark-3.4_2.12;1.6.0 from central in [default]\n",
      "\torg.apache.sedona#sedona-spark-common-3.4_2.12;1.6.0 from central in [default]\n",
      "\torg.beryx#awt-color-factory;1.0.0 from central in [default]\n",
      "\torg.checkerframework#checker-qual;3.10.0 from central in [default]\n",
      "\torg.codehaus.mojo#animal-sniffer-annotations;1.14 from central in [default]\n",
      "\torg.datasyslab#geotools-wrapper;1.6.0-28.2 from central in [default]\n",
      "\torg.locationtech.jts#jts-core;1.19.0 from central in [default]\n",
      "\torg.locationtech.spatial4j#spatial4j;0.8 from central in [default]\n",
      "\torg.scala-lang.modules#scala-collection-compat_2.12;2.5.0 from central in [default]\n",
      "\torg.wololo#jts2geojson;0.16.1 from central in [default]\n",
      "\tuk.co.gresearch.spark#spark-extension_2.12;2.11.0-3.4 from central in [default]\n",
      "\t:: evicted modules:\n",
      "\torg.checkerframework#checker-qual;2.0.0 by [org.checkerframework#checker-qual;3.10.0] in [default]\n",
      "\tcom.google.errorprone#error_prone_annotations;2.1.3 by [com.google.errorprone#error_prone_annotations;2.5.1] in [default]\n",
      "\t---------------------------------------------------------------------\n",
      "\t|                  |            modules            ||   artifacts   |\n",
      "\t|       conf       | number| search|dwnlded|evicted|| number|dwnlded|\n",
      "\t---------------------------------------------------------------------\n",
      "\t|      default     |   25  |   0   |   0   |   2   ||   23  |   0   |\n",
      "\t---------------------------------------------------------------------\n",
      ":: retrieving :: org.apache.spark#spark-submit-parent-32c6a229-d04d-446c-b16d-58a8e96cbc3f\n",
      "\tconfs: [default]\n",
      "\t0 artifacts copied, 23 already retrieved (0kB/81ms)\n",
      "24/05/22 18:08:33 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "config = SedonaContext.builder() .\\\n",
    "    config(\"spark.hadoop.fs.s3a.aws.credentials.provider\", \"org.apache.hadoop.fs.s3a.AnonymousAWSCredentialsProvider\"). \\\n",
    "    config(\"fs.s3a.aws.credentials.provider\", \"org.apache.hadoop.fs.s3a.AnonymousAWSCredentialsProvider\"). \\\n",
    "    config('spark.jars.packages',\n",
    "           'org.apache.sedona:sedona-spark-3.4_2.12:1.6.0,'\n",
    "           'org.datasyslab:geotools-wrapper:1.6.0-28.2,'\n",
    "           'uk.co.gresearch.spark:spark-extension_2.12:2.11.0-3.4'). \\\n",
    "    getOrCreate()\n",
    "\n",
    "sedona = SedonaContext.create(config)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f3340ee",
   "metadata": {},
   "source": [
    "## Spatial filter by boundary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b1b506c",
   "metadata": {},
   "source": [
    "### Pick a boundary\n",
    "\n",
    "Bellevue city is selected\n",
    "\n",
    "[Click here for boundaries of other states](https://gist.github.com/JoshuaCarroll/49630cbeeb254a49986e939a26672e9c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "045f24b3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Washington state boundary\n",
    "#spatial_filter = \"POLYGON((-123.3208 49.0023,-123.0338 49.0027,-122.0650 49.0018,-121.7491 48.9973,-121.5912 48.9991,-119.6082 49.0009,-118.0378 49.0005,-117.0319 48.9996,-117.0415 47.9614,-117.0394 46.5060,-117.0394 46.4274,-117.0621 46.3498,-117.0277 46.3384,-116.9879 46.2848,-116.9577 46.2388,-116.9659 46.2022,-116.9254 46.1722,-116.9357 46.1432,-116.9584 46.1009,-116.9762 46.0785,-116.9433 46.0537,-116.9165 45.9960,-118.0330 46.0008,-118.9867 45.9998,-119.1302 45.9320,-119.1708 45.9278,-119.2559 45.9402,-119.3047 45.9354,-119.3644 45.9220,-119.4386 45.9172,-119.4894 45.9067,-119.5724 45.9249,-119.6013 45.9196,-119.6700 45.8565,-119.8052 45.8479,-119.9096 45.8278,-119.9652 45.8245,-120.0710 45.7852,-120.1705 45.7623,-120.2110 45.7258,-120.3628 45.7057,-120.4829 45.6951,-120.5942 45.7469,-120.6340 45.7460,-120.6924 45.7143,-120.8558 45.6721,-120.9142 45.6409,-120.9471 45.6572,-120.9787 45.6419,-121.0645 45.6529,-121.1469 45.6078,-121.1847 45.6083,-121.2177 45.6721,-121.3392 45.7057,-121.4010 45.6932,-121.5328 45.7263,-121.6145 45.7091,-121.7361 45.6947,-121.8095 45.7067,-121.9338 45.6452,-122.0451 45.6088,-122.1089 45.5833,-122.1426 45.5838,-122.2009 45.5660,-122.2641 45.5439,-122.3321 45.5482,-122.3795 45.5756,-122.4392 45.5636,-122.5676 45.6006,-122.6891 45.6236,-122.7647 45.6582,-122.7750 45.6817,-122.7619 45.7613,-122.7962 45.8106,-122.7839 45.8642,-122.8114 45.9120,-122.8148 45.9612,-122.8587 46.0160,-122.8848 46.0604,-122.9034 46.0832,-122.9597 46.1028,-123.0579 46.1556,-123.1210 46.1865,-123.1664 46.1893,-123.2810 46.1446,-123.3703 46.1470,-123.4314 46.1822,-123.4287 46.2293,-123.4946 46.2691,-123.5557 46.2582,-123.6209 46.2573,-123.6875 46.2497,-123.7404 46.2691,-123.8729 46.2350,-123.9292 46.2383,-123.9711 46.2677,-124.0212 46.2924,-124.0329 46.2653,-124.2444 46.2596,-124.2691 46.4312,-124.3529 46.8386,-124.4380 47.1832,-124.5616 47.4689,-124.7566 47.8012,-124.8679 48.0423,-124.8679 48.2457,-124.8486 48.3727,-124.7539 48.4984,-124.4174 48.4096,-124.2389 48.3599,-124.0116 48.2964,-123.9141 48.2795,-123.5413 48.2247,-123.3998 48.2539,-123.2501 48.2841,-123.1169 48.4233,-123.1609 48.4533,-123.2220 48.5548,-123.2336 48.5902,-123.2721 48.6901,-123.0084 48.7675,-123.0084 48.8313,-123.3215 49.0023,-123.3208 49.0023))\"\n",
    "\n",
    "# Bellevue city boundary\n",
    "spatial_filter = \"POLYGON ((-122.235128 47.650163, -122.233796 47.65162, -122.231581 47.653287, -122.228514 47.65482, -122.227526 47.655204, -122.226175 47.655729, -122.222039 47.656743999999996, -122.218428 47.657464, -122.217026 47.657506, -122.21437399999999 47.657588, -122.212091 47.657464, -122.212135 47.657320999999996, -122.21092999999999 47.653552, -122.209834 47.650121, -122.209559 47.648976, -122.209642 47.648886, -122.21042 47.648658999999995, -122.210897 47.64864, -122.211005 47.648373, -122.21103099999999 47.648320999999996, -122.211992 47.64644, -122.212457 47.646426, -122.212469 47.646392, -122.212469 47.646088999999996, -122.212471 47.645213, -122.213115 47.645212, -122.213123 47.644576, -122.21352999999999 47.644576, -122.213768 47.644560999999996, -122.21382 47.644560999999996, -122.21382 47.644456999999996, -122.21373299999999 47.644455, -122.213748 47.643102999999996, -122.213751 47.642790999999995, -122.213753 47.642716, -122.213702 47.642697999999996, -122.213679 47.642689999999995, -122.21364 47.642678, -122.213198 47.642541, -122.213065 47.642500000000005, -122.212918 47.642466, -122.21275 47.642441, -122.212656 47.642433, -122.21253899999999 47.642429, -122.212394 47.64243, -122.212182 47.642444999999995, -122.211957 47.642488, -122.211724 47.642551999999995, -122.21143599999999 47.642647, -122.210906 47.642834, -122.210216 47.643099, -122.209858 47.643215, -122.20973000000001 47.643248, -122.20973599999999 47.643105, -122.209267 47.643217, -122.208832 47.643302, -122.208391 47.643347999999996, -122.207797 47.643414, -122.207476 47.643418, -122.20701199999999 47.643397, -122.206795 47.643387999999995, -122.205742 47.643246, -122.20549 47.643201999999995, -122.20500200000001 47.643119, -122.204802 47.643085, -122.204641 47.643066, -122.204145 47.643012, -122.203547 47.643012, -122.203097 47.643107, -122.20275699999999 47.643283, -122.202507 47.643496999999996, -122.202399 47.643653, -122.202111 47.643771, -122.201668 47.643767, -122.201363 47.643665, -122.20133 47.643648999999996, -122.201096 47.643536, -122.200744 47.64328, -122.200568 47.64309, -122.200391 47.642849, -122.200162 47.642539, -122.199896 47.642500000000005, -122.19980799999999 47.642424, -122.199755 47.642376999999996, -122.199558 47.642227999999996, -122.199439 47.642157, -122.199293 47.642078999999995, -122.199131 47.642004, -122.198928 47.641925, -122.19883 47.641892, -122.19856300000001 47.641811999999994, -122.198203 47.641731, -122.197662 47.641619999999996, -122.196819 47.641436, -122.196294 47.641309, -122.196294 47.642314, -122.19628 47.642855, -122.196282 47.642897999999995, -122.196281 47.643111, -122.196283 47.643415, -122.196283 47.643508999999995, -122.19628399999999 47.643739, -122.196287 47.644203999999995, -122.196287 47.644262999999995, -122.19629 47.644937999999996, -122.19629 47.644954999999996, -122.196292 47.645271, -122.196291 47.645426, -122.19629499999999 47.646315, -122.19629499999999 47.646432, -122.195925 47.646432, -122.195251 47.646432, -122.190853 47.646429999999995, -122.187649 47.646428, -122.187164 47.646426, -122.18683 47.646426, -122.185547 47.646409, -122.185546 47.646316, -122.185537 47.645599, -122.185544 47.644197, -122.185537 47.643294999999995, -122.185544 47.642733, -122.185541 47.641757, -122.185555 47.640681, -122.185561 47.63972, -122.185557 47.638228999999995, -122.185591 47.635419, -122.185611 47.634750999999994, -122.18562299999999 47.634484, -122.18561700000001 47.634375999999996, -122.185592 47.634311, -122.185549 47.634232999999995, -122.185504 47.634181999999996, -122.185426 47.634119, -122.184371 47.633424999999995, -122.18400000000001 47.633198, -122.183896 47.633134, -122.1838 47.633067, -122.18375499999999 47.633019999999995, -122.183724 47.632959, -122.183695 47.632858, -122.183702 47.632675, -122.182757 47.632622999999995, -122.182365 47.63259, -122.18220600000001 47.632562, -122.181984 47.632504999999995, -122.18163799999999 47.632363, -122.18142 47.632262999999995, -122.181229 47.632165, -122.181612 47.632172999999995, -122.18271899999999 47.632151, -122.183138 47.632135, -122.18440000000001 47.632081, -122.184743 47.632065999999995, -122.185312 47.63205, -122.185624 47.632047, -122.185625 47.631873999999996, -122.184618 47.63187, -122.184291 47.631878, -122.184278 47.631817999999996, -122.183882 47.629942, -122.182689 47.623548, -122.182594 47.622789999999995, -122.182654 47.622155, -122.183135 47.622372999999996, -122.183471 47.622506, -122.18360200000001 47.622552, -122.183893 47.622637999999995, -122.184244 47.62272, -122.184618 47.622777, -122.184741 47.622727999999995, -122.184605 47.622679, -122.18424 47.622622, -122.183985 47.622569, -122.183717 47.622501, -122.183506 47.622439, -122.18327 47.622357, -122.18305699999999 47.622271999999995, -122.182669 47.622088999999995, -122.182796 47.621545, -122.18347 47.619628999999996, -122.18365 47.619098, -122.183859 47.6184, -122.183922 47.617793999999996, -122.183956 47.617292, -122.183792 47.616388, -122.183261 47.614391999999995, -122.183202 47.613802, -122.183209 47.613155, -122.183436 47.612384999999996, -122.18395100000001 47.610445999999996, -122.184338 47.60924, -122.184657 47.609116, -122.18481 47.609051, -122.18491900000001 47.608987, -122.184974 47.608942, -122.185047 47.608846, -122.185082 47.608743999999994, -122.185109 47.608526999999995, -122.185116 47.608359, -122.18513 47.608315999999995, -122.185157 47.608273999999994, -122.185183 47.608247, -122.185246 47.608214, -122.185354 47.608196, -122.185475 47.608191999999995, -122.185472 47.606697, -122.185472 47.606373999999995, -122.185521 47.606272, -122.185528 47.606210999999995, -122.185506 47.606037, -122.185451 47.605872999999995, -122.185411 47.605781, -122.185358 47.605681999999995, -122.185248 47.605509999999995, -122.185127 47.605365, -122.185058 47.605292, -122.184772 47.605038, -122.184428 47.604834, -122.184122 47.604693999999995, -122.183775 47.604574, -122.183644 47.604546, -122.183708 47.604400999999996, -122.183749 47.604223999999995, -122.18376 47.604037, -122.183707 47.603778, -122.183619 47.603556999999995, -122.183559 47.603406, -122.183488 47.603303, -122.183824 47.603167, -122.184108 47.603052, -122.184478 47.602902, -122.18543 47.602495, -122.186669 47.601957, -122.186433 47.601220999999995, -122.186341 47.601127999999996, -122.18874199999999 47.593742999999996, -122.188434 47.592338999999996, -122.188479 47.591786, -122.188217 47.591269999999994, -122.18795399999999 47.590871, -122.186822 47.589228, -122.187421 47.589228999999996, -122.18848299999999 47.589228999999996, -122.188433 47.587922999999996, -122.18990000000001 47.588547, -122.191368 47.589169999999996, -122.19158 47.589222, -122.191779 47.589254999999994, -122.192117 47.589289, -122.191569 47.587478999999995, -122.191323 47.586628999999995, -122.191295 47.586554, -122.191268 47.586479, -122.191192 47.586318, -122.191163 47.586268999999994, -122.1911 47.586164, -122.19099 47.586011, -122.19067 47.585668999999996, -122.1905 47.585515, -122.190301 47.58531, -122.190143 47.585152, -122.189573 47.584576999999996, -122.188702 47.583735999999995, -122.188646 47.583679, -122.188239 47.583258, -122.188037 47.583005, -122.187832 47.582657, -122.187726 47.582164999999996, -122.18769499999999 47.581964, -122.18768299999999 47.581781, -122.187678 47.581592, -122.18766099999999 47.581455, -122.187674 47.581311, -122.18768 47.581146, -122.187722 47.580877, -122.187817 47.580569999999994, -122.187932 47.580301999999996, -122.188047 47.580087, -122.188161 47.579933999999994, -122.188399 47.579660999999994, -122.18851699999999 47.579547, -122.188621 47.579454, -122.188042 47.579493, -122.18762 47.579527, -122.187806 47.579358, -122.188009 47.579175, -122.18814499999999 47.579051, -122.188177 47.579021, -122.18842000000001 47.5788, -122.188638 47.578461, -122.188895 47.57806, -122.189791 47.577281, -122.190008 47.577103, -122.190372 47.576805, -122.19119 47.576358, -122.191877 47.576087, -122.193025 47.57566, -122.194317 47.575185999999995, -122.196061 47.574664, -122.197239 47.574386999999994, -122.197873 47.574267, -122.198286 47.574189999999994, -122.199091 47.574044, -122.199067 47.574574999999996, -122.199007 47.575921, -122.200335 47.578222, -122.20057299999999 47.578345999999996, -122.2009 47.578517999999995, -122.201095 47.578621999999996, -122.20138399999999 47.578776999999995, -122.201465 47.57882, -122.201516 47.578846999999996, -122.205753 47.581112, -122.209515 47.583124, -122.210634 47.583721, -122.21473399999999 47.587021, -122.21538699999999 47.588254, -122.21580399999999 47.589042, -122.216534 47.590421, -122.220092 47.596261, -122.220434 47.596821, -122.22041899999999 47.597837999999996, -122.220289 47.606455, -122.220234 47.610121, -122.22048 47.615221999999996, -122.220359 47.615379, -122.220283 47.615477999999996, -122.21999 47.615854999999996, -122.219993 47.61597, -122.22023300000001 47.616634, -122.220356 47.616687999999996, -122.220409 47.616712, -122.221401 47.618538, -122.22142 47.618573, -122.221456 47.618635, -122.221791 47.619222, -122.222492 47.619682999999995, -122.222799 47.619886, -122.222083 47.620368, -122.222046 47.620407, -122.222028 47.620449, -122.222025 47.620483, -122.22203999999999 47.620523999999996, -122.222079 47.620557999999996, -122.222156 47.620594999999994, -122.222458 47.620629, -122.222454 47.620673, -122.222454 47.620711, -122.22244599999999 47.621041999999996, -122.223056 47.621041, -122.223129 47.62104, -122.223153 47.62104, -122.223574 47.621041, -122.22377900000001 47.621041, -122.223857 47.621041, -122.22467499999999 47.621041, -122.224712 47.62104, -122.224958 47.62104, -122.225167 47.621049, -122.226882 47.621037, -122.227565 47.621032, -122.228002 47.621029, -122.22797800000001 47.621300999999995, -122.227919 47.626574999999995, -122.227914 47.627085, -122.227901 47.6283, -122.227881 47.630069, -122.227869 47.631177, -122.227879 47.631952999999996, -122.22789 47.633879, -122.227886 47.63409, -122.227871 47.635534, -122.227918 47.635565, -122.228953 47.635624, -122.22895199999999 47.635571999999996, -122.231018 47.635574999999996, -122.233276 47.635588999999996, -122.233287 47.63617, -122.233273 47.63639, -122.233272 47.636469999999996, -122.23327 47.636578, -122.233266 47.636827, -122.233263 47.636851, -122.233262 47.637014, -122.23322999999999 47.638110999999995, -122.233239 47.638219, -122.233262 47.638279, -122.233313 47.638324999999995, -122.233255 47.638359, -122.233218 47.638380999999995, -122.233153 47.638450999999996, -122.233136 47.638552999999995, -122.233137 47.638692, -122.232715 47.639348999999996, -122.232659 47.640093, -122.232704 47.641375, -122.233821 47.645111, -122.234906 47.648874, -122.234924 47.648938, -122.235128 47.650163))\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63033ca3",
   "metadata": {},
   "source": [
    "### Visualizing Overture Maps\n",
    "\n",
    "Explanation to the each step is similar across the different datasets. [Click here to learn more about Overture Maps.](https://docs.overturemaps.org)\n",
    "\n",
    "1. `df = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=XX/type=YY\")`\n",
    "\n",
    "    It reads the dataset mentioned by theme and type, that's stored in GeoParquet format.\n",
    "\n",
    "\n",
    "2. `df = df.filter(\"ST_Contains(ST_GeomFromWKT('\"+state_boundary+\"'), geometry) = true\")`\n",
    "    \n",
    "    This filters out all the data that is not in the mentioned `state_boundary` string. Please select a state as you wish.\n",
    "    \n",
    "    ST_GeomFromWKT() - constructs a geometry from WKT (Well Known Text)\n",
    "    \n",
    "    ST_Contains(A, B) - checks if A fully contains B and returns True\n",
    "\n",
    "3. `XX_geom = df.selectExpr(\"geometry\")`\n",
    "\n",
    "    Storing geometry column for SedonaKepler.\n",
    "    \n",
    "\n",
    "5. `map = SedonaKepler.create_map(XX_geom, 'XX')`\n",
    "\n",
    "    Creating a map object using SedonaKepler with inputs geometry column and the name of dataset.\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d2cd7e5",
   "metadata": {},
   "source": [
    "## Place Dataset "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea27a0fc-88f6-4a0d-82e6-82108694265e",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Inspect the metadata of GeoParquet files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49a4e695-7c34-4e6a-9758-f46f6e94b8ac",
   "metadata": {},
   "source": [
    "Inspect the parquet metadata of the building dataset using spark-extension. Detailed usage can be found here: https://github.com/G-Research/spark-extension/blob/master/PARQUET.md"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1b2bdd89-2a3f-406b-a655-1892cfcdc0ed",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/05/22 18:09:00 WARN MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-s3a-file-system.properties,hadoop-metrics2.properties\n",
      "[Stage 5:>                                                          (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+----------+---------------+-----------------+------+-------+--------+-------+\n",
      "|            filename|block|blockStart|compressedBytes|uncompressedBytes|  rows|columns|  values|  nulls|\n",
      "+--------------------+-----+----------+---------------+-----------------+------+-------+--------+-------+\n",
      "|s3a://wherobots-e...|    1|         4|      133401158|        212026494|672280|     26|25498425|3949559|\n",
      "|s3a://wherobots-e...|    2| 133401162|      117481155|        186628211|592107|     26|22456535|3480966|\n",
      "|s3a://wherobots-e...|    1|         4|      132009298|        217901239|739959|     26|28171336|4659055|\n",
      "|s3a://wherobots-e...|    2| 132009302|       27865148|         44972145|155610|     26| 5925253| 980001|\n",
      "|s3a://wherobots-e...|    1|         4|      131924175|        210390680|679198|     26|26835822|4204847|\n",
      "|s3a://wherobots-e...|    2| 131924179|      100376907|        159779330|516904|     26|20420533|3199442|\n",
      "|s3a://wherobots-e...|    1|         4|      133368917|        212391474|710100|     26|26798558|4480776|\n",
      "|s3a://wherobots-e...|    2| 133368921|       68844967|        109127771|366446|     26|13827983|2312016|\n",
      "|s3a://wherobots-e...|    1|         4|      133624141|        224974977|671721|     26|28791936|4047492|\n",
      "|s3a://wherobots-e...|    2| 133624145|       78353690|        131116926|393520|     26|16855422|2370775|\n",
      "|s3a://wherobots-e...|    1|         4|      132531884|        212087729|673983|     26|27189443|4181539|\n",
      "|s3a://wherobots-e...|    2| 132531888|       85813847|        136921776|436523|     26|17610252|2708624|\n",
      "|s3a://wherobots-e...|    1|         4|      132240373|        228506339|679198|     26|24892057|4556778|\n",
      "|s3a://wherobots-e...|    2| 132240377|       77948324|        134442494|400011|     26|14661020|2682371|\n",
      "|s3a://wherobots-e...|    1|         4|      133097687|        217681358|690100|     26|25177484|4427618|\n",
      "|s3a://wherobots-e...|    2| 133097691|       10383388|         16254438| 53363|     26| 1946918| 342463|\n",
      "|s3a://wherobots-e...|    1|         4|      132977557|        221242036|673983|     26|24519195|4518407|\n",
      "|s3a://wherobots-e...|    2| 132977561|       23099608|         37806057|116767|     26| 4246990| 782901|\n",
      "|s3a://wherobots-e...|    1|         4|      131748222|        207244630|658924|     26|26686490|4121025|\n",
      "|s3a://wherobots-e...|    2| 131748226|      131665990|        207128595|658924|     26|26681616|4123667|\n",
      "+--------------------+-----+----------+---------------+-----------------+------+-------+--------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "sedona.read.parquet_blocks(DATA_LINK+\"theme=places/type=place\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea862955-a338-452d-8b99-2ed2e49a35b5",
   "metadata": {
    "tags": []
   },
   "source": [
    "Inspect the GeoParquet metadata using Sedona `geoparquet.metadata`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5cf0ee65-2ec3-4f8d-937e-458005d391f3",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- version: string (nullable = true)\n",
      " |-- primary_column: string (nullable = true)\n",
      " |-- columns: map (nullable = true)\n",
      " |    |-- key: string\n",
      " |    |-- value: struct (valueContainsNull = true)\n",
      " |    |    |-- encoding: string (nullable = true)\n",
      " |    |    |-- geometry_types: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- bbox: array (nullable = true)\n",
      " |    |    |    |-- element: double (containsNull = true)\n",
      " |    |    |-- crs: string (nullable = true)\n",
      " |-- geohash: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sedona.read.format(\"geoparquet.metadata\").load(DATA_LINK+\"theme=places/type=place\").drop(\"path\").printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c0916fc4-3ecb-4cb8-88f3-d8822fa755e1",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 8:>                                                          (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+--------------+-----------------------------------------------------------------------------------------+-------+\n",
      "|version     |primary_column|columns                                                                                  |geohash|\n",
      "+------------+--------------+-----------------------------------------------------------------------------------------+-------+\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-45.0, -16.8737144, -33.7740679, -11.2501301], null}}       |7j     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-135.0, -83.8333, -124.4478565, -78.7677918], null}}        |11     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-145.546875, -66.4255372, -135.3419066, -62.6119407], null}}|0u     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-168.75, -27.8467506, -158.4935007, -22.5328537], null}}    |27     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [68.203125, -44.3395652, 78.3984375, -39.5638609], null}}    |m8     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [33.75, -16.8686782, 44.9838638, -11.2558997], null}}        |kv     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [78.75, -22.4083013, 89.99897, -16.968308], null}}           |mu     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [80.1560848, 73.5, 89.0321735, 78.1825871], null}}           |vv     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [158.3080636, 0.0, 168.7045937, 5.6247198], null}}           |x8     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [146.65, 67.614791, 156.863333, 72.0234111], null}}          |zk     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [56.25, -67.5, 67.2499575, -61.9483887], null}}              |jk     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [67.5, 67.518, 78.046875, 72.96], null}}                     |vs     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [135.0, 16.953941, 146.0742188, 22.4856433], null}}          |x5     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [90.0, 39.376772, 101.2495333, 44.9817729], null}}           |wp     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [101.25, 45.0, 112.493593, 50.6131607], null}}               |y2     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-67.5, -73.1167, -56.5362777, -67.5167], null}}             |4e     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-78.4263447, 61.90054, -67.6226755, 67.41667], null}}       |f7     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [-56.25, 22.5402227, -45.2120876, 28.1104459], null}}        |du     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [136.7578125, -84.8460811, 142.3828125, -84.3999669], null}} |p0     |\n",
      "|1.0.0-beta.1|geometry      |{geometry -> {WKB, [Point], [90.8843994, -56.1702103, 101.16154, -50.6342525], null}}    |nn     |\n",
      "+------------+--------------+-----------------------------------------------------------------------------------------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "sedona.read.format(\"geoparquet.metadata\").load(DATA_LINK+\"theme=places/type=place\").drop(\"path\").show(truncate = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83c07bb3-08be-45d0-82ce-e665665af175",
   "metadata": {},
   "source": [
    "Inspect the schema of GeoParquet files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6c0a3c6e-db4c-4c59-bfe8-11371a345bdd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: string (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- names: map (nullable = true)\n",
      " |    |-- key: string\n",
      " |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |-- key: string\n",
      " |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- categories: struct (nullable = true)\n",
      " |    |-- main: string (nullable = true)\n",
      " |    |-- alternate: array (nullable = true)\n",
      " |    |    |-- element: string (containsNull = true)\n",
      " |-- confidence: double (nullable = true)\n",
      " |-- websites: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- socials: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- emails: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- phones: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- brand: struct (nullable = true)\n",
      " |    |-- names: map (nullable = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |    |-- key: string\n",
      " |    |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |    |-- wikidata: string (nullable = true)\n",
      " |-- addresses: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=places/type=place\").printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfb1cc80-149c-4523-b674-c0e19155c42d",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Run a spatial range query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a6443d1d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 861 ms, sys: 214 ms, total: 1.08 s\n",
      "Wall time: 3min 22s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_place = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=places/type=place\")\n",
    "\n",
    "df_place = df_place.filter(\"ST_Contains(ST_GeomFromWKT('\"+spatial_filter+\"'), geometry) = true\").cache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1f826cb8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.9 s, sys: 244 ms, total: 2.14 s\n",
      "Wall time: 14min 52s\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "32162a60501b4ecb9abb129d23a4f4d0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(data={'Place': {'index': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 2…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "map_place = SedonaKepler.create_map(df_place, \"Place\")\n",
    "\n",
    "map_place"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a2c0d96-778c-4170-a2f1-21529ce40696",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Write the result back to a small GeoParquet file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f977d0cf-b74b-42fc-ae97-3ce9f9ece0d4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_place.select(\"id\", \"geometry\", \"categories.main\").limit(1000).repartition(1) \\\n",
    "    .write.format(\"geoparquet\").option(\"geoparquet.version\", \"1.0.0\").option(\"geoparquet.crs\", \"\").mode('overwrite').save(\"places.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7aacbda0-2b55-4c3f-87b6-d2d77a778d17",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Convert a part of the result to GeoPandas and save to different file formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2c5e16ee-40d5-4202-83c0-ee36c27dd040",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>geometry</th>\n",
       "      <th>main</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tmp_1C7D119020F10C096A27DBAD487578E3</td>\n",
       "      <td>POINT (-122.22225 47.63574)</td>\n",
       "      <td>cafe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tmp_CABF2AA2D28C364087CA75322925EF3B</td>\n",
       "      <td>POINT (-122.19133 47.63664)</td>\n",
       "      <td>dentist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tmp_4DF5EDF25453A5B26C67DB7DCF4AADEA</td>\n",
       "      <td>POINT (-122.18555 47.63417)</td>\n",
       "      <td>park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tmp_2F03B1439285DF7ED6B85BDB817B38F5</td>\n",
       "      <td>POINT (-122.18565 47.61602)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tmp_A07F048103476EE43B98CD623E0939C7</td>\n",
       "      <td>POINT (-122.20394 47.61775)</td>\n",
       "      <td>fast_food_restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>tmp_0F88FA5D31AADC237ECC31C029737EB0</td>\n",
       "      <td>POINT (-122.20033 47.61743)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>tmp_341F7756223EFDC7FCB60CB22276F67B</td>\n",
       "      <td>POINT (-122.19403 47.61779)</td>\n",
       "      <td>mortgage_broker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>tmp_26900E583436FD0BA86722A6A78309B3</td>\n",
       "      <td>POINT (-122.19101 47.61840)</td>\n",
       "      <td>bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>tmp_FF38B028F3C5ED0C3919C357BAF09315</td>\n",
       "      <td>POINT (-122.19332 47.62042)</td>\n",
       "      <td>bubble_tea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>tmp_683827FB15AFF93F5530B3096DD86828</td>\n",
       "      <td>POINT (-122.18405 47.62007)</td>\n",
       "      <td>lighting_store</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       id                     geometry  \\\n",
       "0    tmp_1C7D119020F10C096A27DBAD487578E3  POINT (-122.22225 47.63574)   \n",
       "1    tmp_CABF2AA2D28C364087CA75322925EF3B  POINT (-122.19133 47.63664)   \n",
       "2    tmp_4DF5EDF25453A5B26C67DB7DCF4AADEA  POINT (-122.18555 47.63417)   \n",
       "3    tmp_2F03B1439285DF7ED6B85BDB817B38F5  POINT (-122.18565 47.61602)   \n",
       "4    tmp_A07F048103476EE43B98CD623E0939C7  POINT (-122.20394 47.61775)   \n",
       "..                                    ...                          ...   \n",
       "995  tmp_0F88FA5D31AADC237ECC31C029737EB0  POINT (-122.20033 47.61743)   \n",
       "996  tmp_341F7756223EFDC7FCB60CB22276F67B  POINT (-122.19403 47.61779)   \n",
       "997  tmp_26900E583436FD0BA86722A6A78309B3  POINT (-122.19101 47.61840)   \n",
       "998  tmp_FF38B028F3C5ED0C3919C357BAF09315  POINT (-122.19332 47.62042)   \n",
       "999  tmp_683827FB15AFF93F5530B3096DD86828  POINT (-122.18405 47.62007)   \n",
       "\n",
       "                     main  \n",
       "0                    cafe  \n",
       "1                 dentist  \n",
       "2                    park  \n",
       "3                    None  \n",
       "4    fast_food_restaurant  \n",
       "..                    ...  \n",
       "995                  None  \n",
       "996       mortgage_broker  \n",
       "997                bakery  \n",
       "998            bubble_tea  \n",
       "999        lighting_store  \n",
       "\n",
       "[1000 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf = gpd.GeoDataFrame(df_place.select(\"id\", \"geometry\", \"categories.main\").limit(1000).toPandas(), geometry=\"geometry\")\n",
    "gdf.to_file('places.geojson', driver='GeoJSON')\n",
    "gdf.to_file('places.shp')\n",
    "gdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31e81f8b",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Building Dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d0c55157",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 474 ms, sys: 92.9 ms, total: 567 ms\n",
      "Wall time: 1min 48s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_building = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=buildings/type=building\")\n",
    "\n",
    "df_building = df_building.filter(\"ST_Contains(ST_GeomFromWKT('\"+spatial_filter+\"'), geometry) = true\")\n",
    "\n",
    "df_building = df_building.limit(200_000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bb62f16e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/srv/conda/envs/notebook/lib/python3.9/site-packages/jupyter_client/session.py:718: UserWarning: Message serialization failed with:\n",
      "Out of range float values are not JSON compliant\n",
      "Supporting this message is deprecated in jupyter-client 7, please make sure your message is JSON-compliant\n",
      "  content = self.pack(content)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.91 s, sys: 590 ms, total: 4.5 s\n",
      "Wall time: 25min 30s\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1319d73c966641108745c145306ec39d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(data={'Building': {'index': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "map_building = SedonaKepler.create_map(df_building, 'Building')\n",
    "map_building"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0809afb",
   "metadata": {},
   "source": [
    "## Admins Theme Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "930b6c81",
   "metadata": {},
   "source": [
    "## Administrative Boundary Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6b9d6296",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 272 ms, sys: 57.9 ms, total: 330 ms\n",
      "Wall time: 1min 1s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_admin = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=admins/type=administrativeBoundary\")\n",
    "\n",
    "df_admin = df_admin.filter(\"ST_Contains(ST_GeomFromWKT('\"+spatial_filter+\"'), geometry) = true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2ce9597a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 34:==================================================>     (10 + 1) / 11]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 96.4 ms, sys: 37.9 ms, total: 134 ms\n",
      "Wall time: 4min 3s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9dc08c20124b4d12b043c684a336c346",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(data={'Admin': {'index': [], 'columns': ['id', 'updatetime', 'version', 'names', 'adminlevel', 'marit…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "map_admin = SedonaKepler.create_map(df_admin, \"Admin\")\n",
    "\n",
    "map_admin"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0acad0d7",
   "metadata": {},
   "source": [
    "## Locality Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8541ada7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 280 ms, sys: 59 ms, total: 339 ms\n",
      "Wall time: 1min 5s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_locality = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=admins/type=locality\")\n",
    "\n",
    "df_locality = df_locality.filter(\"ST_Contains(ST_GeomFromWKT('\"+spatial_filter+\"'), geometry) = true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0c5c3bf3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 37:====================================================>   (14 + 1) / 15]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 130 ms, sys: 28.7 ms, total: 158 ms\n",
      "Wall time: 4min 47s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7256f7f3f2ee49e7b8be3382f51b5636",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(data={'Locality': {'index': [], 'columns': ['id', 'updatetime', 'version', 'names', 'adminlevel', 'ma…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "map_locality = SedonaKepler.create_map(df_locality, 'Locality')\n",
    "\n",
    "map_locality"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "328ffd56",
   "metadata": {},
   "source": [
    "## Transportation Theme Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b914f57",
   "metadata": {},
   "source": [
    "## Connector Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "224167dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 409 ms, sys: 93.6 ms, total: 502 ms\n",
      "Wall time: 1min 42s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_connector = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=transportation/type=connector\")\n",
    "\n",
    "df_connector = df_connector.filter(\"ST_Contains(ST_GeomFromWKT('\"+spatial_filter+\"'), geometry) = true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e5f8de53",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.92 s, sys: 139 ms, total: 2.06 s\n",
      "Wall time: 9min 52s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/srv/conda/envs/notebook/lib/python3.9/site-packages/jupyter_client/session.py:718: UserWarning: Message serialization failed with:\n",
      "Out of range float values are not JSON compliant\n",
      "Supporting this message is deprecated in jupyter-client 7, please make sure your message is JSON-compliant\n",
      "  content = self.pack(content)\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b6893ff77fac4b82bb9f59b7d481e8b1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(data={'Connector': {'index': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "map_connector = SedonaKepler.create_map(df_connector.drop(\"updatetime\"), \"Connector\")\n",
    "\n",
    "map_connector"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1e7ab2d",
   "metadata": {},
   "source": [
    "## Segment Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ba8eff7c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 502 ms, sys: 79.8 ms, total: 582 ms\n",
      "Wall time: 1min 45s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_segment = sedona.read.format(\"geoparquet\").load(DATA_LINK+\"theme=transportation/type=segment\")\n",
    "\n",
    "df_segment = df_segment.filter(\"ST_Contains(ST_GeomFromWKT('\"+spatial_filter+\"'), geometry) = true\")\n",
    "\n",
    "df_segment = df_segment.limit(200000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a9bf0862",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User Guide: https://docs.kepler.gl/docs/keplergl-jupyter\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.63 s, sys: 335 ms, total: 2.96 s\n",
      "Wall time: 15min 50s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/srv/conda/envs/notebook/lib/python3.9/site-packages/jupyter_client/session.py:718: UserWarning: Message serialization failed with:\n",
      "Out of range float values are not JSON compliant\n",
      "Supporting this message is deprecated in jupyter-client 7, please make sure your message is JSON-compliant\n",
      "  content = self.pack(content)\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "762bdadc78fc42209fd9009fc18d9df9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "KeplerGl(data={'Segment': {'index': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "map_segment = SedonaKepler.create_map(df_segment.drop(\"updatetime\"), \"Segment\")\n",
    "\n",
    "map_segment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf6ef3b0-d76a-4ae1-a96e-8077a7a64581",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
